#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : day_report_channel_house.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-06-25 17:39 
@Desc    : 
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util
from report_system.utils import mail_util
from report_system.utils import date_util
from report_system.utils.log_util import log
from report_system.utils.development_util import debug

# from report_system.utils.enum_util import DAY_REPORT_CHANNEL_COLOR

if debug:
    receivers = "bianzhiwei@iyoujia.com"
else:
    receivers = 'yangweiwei@iyoujia.com;songweiyun@iyoujia.com;zhuqiang@iyoujia.com;kf@iyoujia.com;' \
                'yy@iyoujia.com;dt@iyoujia.com'

log.info("收件人为: " + receivers)


def channel_order_sql(st, et):
    sql = """
    SELECT 
    concat(whs.house_id,"") 房屋id,
    concat(whs.lodge_id,"") 房源id,
    whs.city_name 城市, 
    whs.title 房源标题,
    whs.service_name 服务中心,
    whs.work_name 门店,
    whs.online_sale 线上销售,
    whs.offline_sale 线下管家,
    sc.name 渠道,
    count(1) 下单数,
    sum(if(bo.brush_status <> 1
                    AND (bo.pay_state = 1  or (bo.pay_state = 0 and  bo.order_channel=5))
                    AND bo.confirm_status = 1 
                    AND bo.cancel_status = 0 ,1,0))  成功订单数,
    sum(datediff(bo.checkout_day,bo.checkin_day)) 下单间夜,
    sum(if(bo.brush_status <> 1
                    AND (bo.pay_state = 1  or (bo.pay_state = 0 and  bo.order_channel=5))
                    AND bo.confirm_status = 1 
                    AND bo.cancel_status = 0 ,datediff(actcheckout_day,bo.actcheckin_day),0))  实际入住间夜,
    round(sum(bo.real_income)/100,2) 下单GMV,
    round(sum(if(bo.brush_status <> 1
                    AND (bo.pay_state = 1  or (bo.pay_state = 0 and  bo.order_channel=5))
                    AND bo.confirm_status = 1 
                    AND bo.cancel_status = 0 ,bo.real_income,0))/100,2) 实际GMV,
    round(sum(bo.real_income)/sum(datediff(actcheckout_day,bo.actcheckin_day))/100,2) 下单平均日价,
    round(sum(if(bo.brush_status <> 1
                    AND (bo.pay_state = 1  or (bo.pay_state = 0 and  bo.order_channel=5))
                    AND bo.confirm_status = 1 
                    AND bo.cancel_status = 0 ,bo.real_income,0))
                    /sum(datediff(actcheckout_day,bo.actcheckin_day))/100,2) 实际平均日价
    FROM youjia_report.wh_house_static whs
    left join youjia.orders bo  on whs.lodge_id = bo.lodgeunit_id 
            and date(bo.create_time) between '{st}' and '{et}'
            and bo.city_id <> 376
    left join youjia_common.sales_channel sc on sc.id = bo.order_channel
    where whs.city_id <> 376
    and whs.house_state =100
    and whs.lodge_state = 0
    and whs.lodge_state_super = 10
    group by whs.lodge_id ,bo.order_channel
    having 渠道 is not null;
    """.format(st=st, et=et)
    return sql


def process():
    yesterday = date_util.curdate()
    st = date_util.cur_month_first(1, yesterday)
    et = yesterday
    log.info("yesterday : {yesterday}" .format(yesterday=yesterday))
    log.info("st : {st}" .format(st=st))
    log.info("et : {et}" .format(et=et))
    sql = channel_order_sql(st, et)

    order_df = pd.read_sql_query(sql, db_util.youjia_13_sjz_db)
    log.info(" ------ 查询所有数据完成 ------ ")
    order_df = order_df.set_index(['房屋id', '房源id', '城市', '房源标题', '服务中心', '门店', '线上销售', '线下管家', '渠道'])
    log.info(" ------ 添加索引成功 ------ ")
    df = order_df.unstack()
    df = df.reset_index()
    log.info(" ------ unstack 翻转成功 ------ ")
    # excel_util.pd_to_excel({"房源订单渠道分布.xlsx": df}, "房源订单渠道分布2", engine='xlsxwriter')

    # colors_dict = {'房源订单渠道分布': DAY_REPORT_CHANNEL_COLOR}

    text = """    Dear All:
                附件是房源订单渠道分布！ 
                请查收！
                谢谢！
        如有问题！
        请联系: dt@iyoujia.com
        """
    mail_util.distribute_mail(df_dict={"房源订单渠道分布": df}, file_name='房源订单渠道分布日报', text=text,

                              title="【房源订单渠道分布日报】", receiver=receivers,
                              engine='xlsxwriter')


if __name__ == '__main__':
    process()
